"""
Case Type   : 可靠性-数据库系统类
Case Name   : 事务操作产生日志，删除当前xlog日志文件，数据库重启
Create At   : 2022/07/07
Owner       : @li-xin12345
Description :
    1. 修改checkpoint_segments、checkpoint_timeout参数
    2. 执行事务操作
    3. 查询当前xlog文件
    4. 删除当前xlog日志文件
    5. 启动数据库
    6. 再次插入数据
Expect      :
    1. 修改参数成功
    2. 执行事务操作成功
    3. 查询当前xlog日志成功
    4. 删除当前xlog日志文件成功
    5. 启动数据库成功
    6. 再次插入数据成功
History     :
    Modified by @li-xin12345 at 2023.5.29 优化用例
    Modified by n@ningyali at 2023.9.18 拆分测试点为
        测试点1：未写入WAL的当前xlog文件丢失，数据库重启，预期不影响数据库启动
        测试点2：已写入WAL的当前xlog文件丢失，重启数据库，预期可能会重启失败
    Modified by @ningyali at 2024.7.9 优化用例解决不一致问题
"""
import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSH import RestartDbCluster
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Dbsys(unittest.TestCase):
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_dbuser = self.pri_sh.node
        self.common = Common()
        self.constant = Constant()
        self.t_name = 't_dbsys_case048'
        self.xlog_file_path = ''
        self.bak_xlog_path = ''
        self.init_para1 = self.pri_sh.show_param('checkpoint_segments;')
        self.init_para2 = self.pri_sh.show_param('checkpoint_timeout;')
        self.status = None

    def test_dbsys1(self):
        text = '-----step1:修改checkpoint_segments、checkpoint_timeout参数;' \
               'expect:修改参数成功-----'
        self.log.info(text)
        result = self.pri_sh.executeGsguc('reload',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          'checkpoint_segments=1600')
        self.assertTrue(result, '执行失败' + text)
        show_msg = self.pri_sh.show_param('checkpoint_segments;')
        self.assertEquals('1600', show_msg, '执行失败' + text)
        result = self.pri_sh.executeGsguc('reload',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          'checkpoint_timeout=60min')
        self.assertTrue(result, '执行失败' + text)
        show_msg = self.pri_sh.show_param('checkpoint_timeout;')
        self.assertEquals('1h', show_msg, '执行失败' + text)

        text = '-----step2:执行事务操作;expect:执行事务操作成功-----'
        self.log.info(text)
        sql_cmd = f'''
            select pg_switch_xlog();
            select pg_xlogfile_name(pg_current_xlog_location());
            set synchronous_commit=on;
            begin 
                drop table if exists {self.t_name};
                create table {self.t_name}(id int,name varchar(20));
                for i in 1..100000 loop
                    insert into {self.t_name} values(i,'aaa');
                end loop;
            end;
            select count(*) from {self.t_name};'''
        sql_msg = self.pri_sh.executDbSql(sql_cmd)
        self.log.info(sql_msg)
        self.assertIn('100000', sql_msg.splitlines()[-2], '执行失败' + text)

        text = '-----step3:查询当前xlog文件;expect:查询当前xlog文件成功-----'
        self.log.info(text)
        self.log.info('-----查询当前xlog文件-----')
        xlog_cmd = f"select pg_xlogfile_name(pg_current_xlog_location());"
        sql_msg = self.pri_sh.executDbSql(xlog_cmd)
        self.log.info(sql_msg)
        pg_xlogfile = sql_msg.splitlines()[-2].strip()
        self.xlog_file_path = os.path.join(macro.PG_XLOG_PATH, pg_xlogfile)
        self.log.info(self.xlog_file_path)

        text = '-----step4:删除当前xlog日志文件;expect:删除当前xlog日志文件成功-----'
        self.log.info(text)
        self.log.info('-----备份xlog文件;expect:备份成功-----')
        mkdir_cmd = f'if [ -e {macro.DB_BACKUP_PATH} ]; then echo exists;' \
            f' else  mkdir -p  {macro.DB_BACKUP_PATH}; fi'
        mkdir_msg = self.pri_dbuser.sh(mkdir_cmd).result()
        self.log.info(mkdir_msg)
        if 'exists' not in mkdir_msg:
            self.assertEqual(len(mkdir_msg.strip()), 0, 'error: 创建备份目录失败')

        self.bak_xlog_path = os.path.join(macro.DB_BACKUP_PATH, pg_xlogfile)
        try:
            mv_cmd = f'mv {self.xlog_file_path} {self.bak_xlog_path}'
            self.log.info(mv_cmd)
            mv_msg = self.pri_dbuser.sh(mv_cmd).result()
            self.log.info(mv_msg)

            text = '-----step5:重启数据库;expect:重启数据库成功-----'
            self.log.info(text)
            is_stopped = self.pri_sh.stopDbCluster()
            self.log.info(is_stopped)
            is_started = self.pri_sh.startDbCluster()
            self.log.info(is_started)
            self.assertTrue(is_stopped, '停库失败')

            self.status = self.pri_sh.getDbClusterStatus('status')
            self.assertTrue(self.status, '数据库状态异常')
        finally:
            if not self.status:
                text = '----异常场景，恢复xlog日志文件; expect:再次重启集群----'
                self.log.info(text)
                mv_cmd = f'mv {self.bak_xlog_path} {self.xlog_file_path}'
                self.log.info(mv_cmd)
                mv_msg = self.pri_dbuser.sh(mv_cmd).result()
                self.log.info(mv_msg)
                is_stopped = self.pri_sh.stopDbCluster()
                self.log.info(is_stopped)
                is_started = self.pri_sh.startDbCluster()
                self.log.info(is_started)
                self.assertTrue(is_started, '启动失败')

            self.log.info('----查询当前xlog文件----')
            sql_msg = self.pri_sh.executDbSql(xlog_cmd)
            self.log.info(sql_msg)

        text = '-----step6:再次插入数据;expect:再次插入数据成功-----'
        self.log.info(text)
        sql_cmd = f'''set synchronous_commit=on;
                    begin 
                        for i in 1..100000 loop 
                            insert into {self.t_name} values(i,'bbb');
                        end loop;
                    end;
                    select count(*) from {self.t_name};'''
        self.log.info(sql_cmd)
        sql_msg = self.pri_sh.executDbSql(sql_cmd)
        self.log.info(sql_msg)
        self.assertIn('200000', sql_msg.splitlines()[-2], '执行失败' + text)

    def tearDown(self):
        self.log.info('-----this is tearDown-----')
        text = '-----step7:删除表，恢复参数;expect:删除表成功，恢复参数成功-----'
        self.log.info(text)
        drop_cmd = f'drop table {self.t_name};'
        drop_msg = self.pri_sh.executDbSql(drop_cmd)
        self.log.info(drop_msg)
        self.pri_sh.executeGsguc('reload', self.constant.GSGUC_SUCCESS_MSG,
                                 f'checkpoint_segments={self.init_para1}')
        segments_msg = self.pri_sh.show_param('checkpoint_segments;')
        self.pri_sh.executeGsguc('reload', self.constant.GSGUC_SUCCESS_MSG,
                                 f'checkpoint_timeout={self.init_para2}')
        timeout_msg = self.pri_sh.show_param('checkpoint_timeout;')

        self.log.info('-----断言tearDown-----')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS,
                      drop_msg, '执行失败' + text)
        self.assertEquals(self.init_para1, segments_msg, '执行失败' + text)
        self.assertEquals(self.init_para2, timeout_msg, '执行失败' + text)
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
